-- EXEC SP_LOAD_INVENTORY_PACKINGSLIP_LOTS 35
ALTER PROC SP_LOAD_INVENTORY_PACKINGSLIP_LOTS( @PackingSlip_ID INT )
AS 
BEGIN
	CREATE TABLE #RESULTS ( LOT_ID INT, PROCESSSTEP_ID INT, LOCATION_ID INT, PART_NUMBER VARCHAR(50), Lot_Number VARCHAR(50), 
		Fab_Lot_Number VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT, DATE_IN DATETIME, VENDOR_NAME VARCHAR(50), DATE_CODE VARCHAR(50), 
		PS_TYPE VARCHAR(50), WAFER_IDS VARCHAR(100), SELECTED_WAFER_IDS VARCHAR(100), LOT_STATUS VARCHAR(50), Ship_Qty INT, Ship_Die_QTY INT, BIN_QTY_STR VARCHAR(200) )

	DECLARE @Lot_Cursor CURSOR
	DECLARE @Lot_Id INT
	DECLARE @Ship_Qty INT
	DECLARE @WaferIds VARCHAR(100)
	DECLARE @SelectedWaferIds VARCHAR(100)
	DECLARE @ProcessStep_Id INT
	DECLARE @Location_Id INT
	DECLARE @WAFER_QTY_IN INT
	DECLARE @QTY_IN INT
	DECLARE @Date_IN DATETIME
	DECLARE @Part_Number VARCHAR(50)
	DECLARE @Lot_Number VARCHAR(50)
	DECLARE @Fab_Lot_Number VARCHAR(50)
	DECLARE @Vendor_Name VARCHAR(50)
	DECLARE @Date_Code VARCHAR(50)
	DECLARE @PS_Type VARCHAR(50)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @Lot_Current_Status VARCHAR(50)
	DECLARE @SELECTED_WAFER_IDS VARCHAR(100)
	DECLARE @WAFERS_TABLE	TABLE ( WAFER_ID INT, QTY_IN INT )
	DECLARE @SELECTED_WAFER_IDS_TABLE TABLE ( WAFER_ID INT)
	DECLARE @QTY_AS_STRING	VARCHAR(200)
	DECLARE @GORSS_DIE_PER_WAFER INT
	DECLARE @WAFER_COUNT		 INT

	DECLARE @WAFER_ID_TABLE	 TABLE ( WAFER_ID INT)
	DECLARE @WAFER_IDS_AS_COMMA VARCHAR(100)
	DECLARE @WAFER_IDS VARCHAR(100)
	DECLARE @WAFER_ID INT
	DECLARE @SHIP_DIE_QTY INT, @SHIP_WAFER_QTY INT

	SET @Lot_Cursor = CURSOR FOR SELECT Lot_ID, Ship_Qty, WaferIds FROM Inventory_PackingSlip_Lot_Table WHERE PackingSlip_Id = @PackingSlip_ID
	OPEN @Lot_Cursor
	FETCH NEXT FROM @Lot_Cursor INTO @Lot_ID, @Ship_Qty, @SelectedWaferIds
	WHILE @@FETCH_STATUS = 0
	BEGIN
	 	SELECT @ProcessStep_Id = LT.PROCESSSTEP_ID, @Location_Id = LT.LOCATION_ID, @Part_Number = PART_NUMBER, 
		@Lot_Number = LOT_NUMBER, @Fab_Lot_Number = FAB_LOT_NUMBER, @WAFER_QTY_IN = WAFER_QTY_IN, @QTY_IN = QTY_IN, @Date_IN = DATE_IN,
		@Vendor_Name = L_VENDOR_NAME, @Date_Code = LOT_DATE_CODE, @PS_Type = PS.PS_Type, @Lot_Current_Status = LOT_CURRENT_STATUS
		FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P
		WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
		LT.LOCATION_ID = L.LOCATION_ID AND Lt.Lot_ID = @Lot_ID
		
		SELECT @PS_TYPE_NAME = BASE_PROCESS_TYPE FROM Process_Types_Table WHERE Process_Type = @PS_TYPE

		SET @SELECTED_WAFER_IDS	= REPLACE (@SelectedWaferIds, ';', ',' )		
		INSERT INTO @SELECTED_WAFER_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( @SELECTED_WAFER_IDS  )
		SELECT @WAFER_COUNT = COUNT(*) FROM @SELECTED_WAFER_IDS_TABLE

		IF @PS_TYPE_NAME = 'Die Bank'	
		BEGIN
			
			INSERT INTO @WAFERS_TABLE (WAFER_ID, QTY_IN) SELECT WAFER_ID, BIN_1_BALANCE_QTY FROM SORT_BIN_RELEASE_TABLE WHERE LOT_ID = @LOT_ID AND BIN_1_BALANCE_QTY > 0 
				AND WAFER_ID IN (SELECT WAFER_ID  FROM @SELECTED_WAFER_IDS_TABLE ) ORDER bY CAST(WAFER_ID AS INT)				

			--SELECT @WAFER_ID_STR = COALESCE( @WAFER_ID_STR + ';' + CONVERT( VARCHAR(2), WAFER_ID ), CONVERT( VARCHAR(2) , WAFER_ID ) )
			--FROM @WAFERS_TABLE ORDER BY WAFER_ID 

			SELECT @QTY_AS_STRING = COALESCE( @QTY_AS_STRING + ';' + CONVERT( VARCHAR(10), QTY_IN ), CONVERT( VARCHAR(10) , QTY_IN ) )
				 FROM @WAFERS_TABLE ORDER BY WAFER_ID

			--SET @WAFER_QTY_IN = @WAFER_COUNT
			DELETE FROM @SELECTED_WAFER_IDS_TABLE
			DELETE FROM @WAFERS_TABLE
		END

		IF @PS_TYPE_NAME IS NOT NULL AND @PS_TYPE_NAME = 'FAB'
		BEGIN
				SELECT @WaferIds = Wafer_Id FROM Fab_Lot WHERE Lot_ID = @LOT_ID
		END
		ELSE IF @PS_TYPE_NAME IS NOT NULL AND ( @PS_TYPE_NAME = 'Wafer Store' OR @PS_TYPE_NAME = 'Die Bank' )
		BEGIN
				SELECT @WaferIds = Wafer_Id FROM Inventory_Lot WHERE Lot_ID = @LOT_ID
		END
		ELSE IF @PS_TYPE_NAME IS NOT NULL AND ( @PS_TYPE_NAME = 'Sort' )
		BEGIN
				SELECT @WaferIds = SL_Wafer_Id FROM Sort_Lot WHERE Lot_ID = @LOT_ID
		END
		
		IF @Lot_Current_Status = 'Close'
		BEGIN
			IF @SelectedWaferIds IS NOT NULL AND LEN( @SelectedWaferIds ) > 0
			BEGIN
				SET @WaferIds = @SelectedWaferIds
				IF @PS_TYPE_NAME != 'Die Bank'
				BEGIN
					SET @WAFER_QTY_IN = @Ship_Qty
					SELECT @GORSS_DIE_PER_WAFER = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT DISTINCT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@ProcessStep_Id,0) WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'
					SET @QTY_IN = @Ship_Qty * @GORSS_DIE_PER_WAFER
				END
			END
			ELSE
			BEGIN
				SET @QTY_IN = @Ship_Qty
			END
		END
		
		
		IF @PS_TYPE_NAME IS NOT NULL AND @PS_TYPE_NAME = 'FAB'
		BEGIN				
				SELECT @GORSS_DIE_PER_WAFER = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = @ProcessStep_Id AND Parameter_Key = 'Gross Die/Wafer'
		END
		ELSE IF @PS_TYPE_NAME IS NOT NULL AND @PS_TYPE_NAME = 'Wafer Store'
		BEGIN
				SELECT @GORSS_DIE_PER_WAFER = Parameter_VALUE FROM PROCESS_PARAMETER WHERE PROCESSSTEP_ID = (SELECT TOP 1 PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @ProcessStep_Id,0 )  WHERE PS_TYPE = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'
		END
		ELSE IF @PS_TYPE = 'SORT'
		BEGIN
			SELECT @GORSS_DIE_PER_WAFER = SUM(BIN_1) FROM INFORMATIVE_BINNING WHERE LOT_ID = @Lot_ID AND Wafer_ID IN( SELECT WAFER_ID FROM @SELECTED_WAFER_IDS_TABLE )
			IF @GORSS_DIE_PER_WAFER IS NULL OR @GORSS_DIE_PER_WAFER <=0 
			BEGIN
				SELECT @GORSS_DIE_PER_WAFER = SUM(BIN_1) FROM INFORMATIVE_BINNING WHERE LOT_ID IN ( SELECT LOT_ID FROM LOT_TABLE WHERE Lot_Number Like SUBSTRING( @LOT_NUMBER, 0, CHARINDEX( '.', @LOT_NUMBER ) )+'%' AND Location_ID IN ( SELECT Location_ID from Location WHERE L_Type = 'SORT' ) ) AND Wafer_ID IN( SELECT WAFER_ID FROM @SELECTED_WAFER_IDS_TABLE ) 
			END
		END	

		IF @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'Wafer Store' 
		BEGIN				
				SET @SHIP_DIE_QTY = @Ship_Qty * @GORSS_DIE_PER_WAFER	
				SET @SHIP_WAFER_QTY = @Ship_Qty
		END
		ELSE IF  @PS_TYPE_NAME = 'SORT' 
		BEGIN
				SET @SHIP_DIE_QTY = @GORSS_DIE_PER_WAFER	
				SET @SHIP_WAFER_QTY = @Ship_Qty
		END
		ELSE IF @PS_TYPE_NAME = 'Die Bank'
		BEGIN
				SET @SHIP_DIE_QTY = @Ship_Qty	
				SET @SHIP_WAFER_QTY = @WAFER_COUNT
		END
		ELSE
		BEGIN
				SET @SHIP_DIE_QTY = @Ship_Qty
		END 

		INSERT INTO #RESULTS VALUES ( @Lot_ID, @ProcessStep_Id, @Location_Id, @Part_Number, @Lot_Number, @Fab_Lot_Number, @WAFER_QTY_IN,
		@QTY_IN,@Date_IN, @Vendor_Name, @Date_Code, @PS_Type, @WaferIds, @SelectedWaferIds, @Lot_Current_Status, @SHIP_WAFER_QTY, @SHIP_DIE_QTY, @QTY_AS_STRING )

		SET @QTY_AS_STRING = NULL
		SET @LOT_ID = NULL
		SET @SelectedWaferIds = NULL
		SET @Ship_Qty = NULL
		SET @QTY_AS_STRING = NULL
		SET @WAFER_COUNT = NULL
		FETCH NEXT FROM @Lot_Cursor INTO @Lot_ID, @Ship_Qty, @SelectedWaferIds
	END

	SELECT LOT_ID, PROCESSSTEP_ID, LOCATION_ID, PART_NUMBER, Lot_Number, Fab_Lot_Number, WAFER_QTY_IN, QTY_IN, CONVERT(VARCHAR,DATE_IN,101) AS DATE_IN,
		 VENDOR_NAME, DATE_CODE, PS_TYPE, WAFER_IDS, SELECTED_WAFER_IDS, LOT_STATUS, Ship_Qty,Ship_Die_QTY,BIN_QTY_STR FROM #RESULTS
END